package cn.ffcs.uec.outStaff.dao.impl;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.ffcs.uec.common.util.PermissionUtil;
import cn.ffcs.uec.outStaff.dao.UecOutStaffDao;
import cn.ffcs.uec.outStaff.model.PictureTable;
import cn.ffcs.uec.outStaff.model.UecOutStaffCostB;
import cn.ffcs.uec.outStaff.model.UecOutStaffCostH;
import cn.ffcs.uec.outStaff.model.UecOutStaffInfo;
import cn.ffcs.uec.outStaff.model.UecOutStaffManage;
import cn.ffcs.uec.outStaff.model.UecOutStaffTramsg;
import cn.ffcs.uec.outStaff.model.UecOutStaffVocqua;
import cn.ffcs.uec.outStaff.model.UecOutStaffWork;
import cn.ffcs.uec.outStaff.vo.UecOutStaffInfoVo;
import cn.ffcs.uom.common.constants.BaseUnitConstants;
import cn.ffcs.uom.common.dao.BaseDaoImpl;
import cn.ffcs.uom.common.util.PlatformUtil;
import cn.ffcs.uom.common.util.StrUtil;
import cn.ffcs.uom.common.vo.PageInfo;

@Repository("uecOutStaffDao")
@Transactional
public class UecOutStaffDaoImpl extends BaseDaoImpl implements UecOutStaffDao {

	@Override
	public UecOutStaffInfo findUecOutStaffInfo(UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_INFO T  WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sb.append(" AND T.STATUS_CD = ? ");
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnCode())) {
				sb.append(" AND T.PSNCODE = ? ");
				params.add(uecOutStaffInfo.getPsnCode());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnName())) {
				sb.append(" AND T.PSNNAME = ? ");
				params.add(StringEscapeUtils.escapeSql(uecOutStaffInfo
						.getPsnName()));
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getIdNum())) {
				sb.append(" AND T.IDNUM = ? ");
				params.add(StringEscapeUtils.escapeSql(uecOutStaffInfo
						.getIdNum()));
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getMobtelePhone())) {
				sb.append(" AND T.MOBTELEPHONE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(uecOutStaffInfo
								.getMobtelePhone()) + "%");
			}
			// sb.append(" order by T2.ENDDATE desc");
			List<UecOutStaffInfo> list = super.jdbcFindList(sb.toString(),
					params, UecOutStaffInfo.class);
			if (list.size() > 0 && list != null) {
				return list.get(0);
			}
		}
		return null;
	}
	/**
	 * 查询list
	 */
	@Override
	public List<UecOutStaffInfo> findUecOutStaffInfoList(UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_INFO T  WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sb.append(" AND T.STATUS_CD = ? ");
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnCode())) {
				sb.append(" AND T.PSNCODE = ? ");
				params.add(uecOutStaffInfo.getPsnCode());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnName())) {
				sb.append(" AND T.PSNNAME = ? ");
				params.add(StringEscapeUtils.escapeSql(uecOutStaffInfo
						.getPsnName()));
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getIdNum())) {
				sb.append(" AND T.IDNUM = ? ");
				params.add(StringEscapeUtils.escapeSql(uecOutStaffInfo
						.getIdNum()));
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getMobtelePhone())) {
				sb.append(" AND T.MOBTELEPHONE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(uecOutStaffInfo
								.getMobtelePhone()) + "%");
			}
			// sb.append(" order by T2.ENDDATE desc");
			List<UecOutStaffInfo> list = super.jdbcFindList(sb.toString(),
					params, UecOutStaffInfo.class);
			return list;
		}
		return null;
	}

	@Override
	public void addUecOutStaffInfo(UecOutStaffInfo uecOutStaffInfo) {
		uecOutStaffInfo.addOnly();
	}

	@Override
	public PageInfo findUecOutStaffWork(UecOutStaffInfo uecOutStaffInfo,
			int current, int pageSize) {

		StringBuilder sb = new StringBuilder(
				" SELECT T.*,ja3.UNITNAME unitname FROM UEC_OUT_STAFF_WORK T "
						+ " left join UEC_PSN_CORP ja3 on (T.PK_CORP = ja3.PK_CORP  )"
						+ "   WHERE T.status_cd = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPkPsnMsg())) {
				sb.append(" AND T.PK_PSN_MSG = ? ");
				params.add(uecOutStaffInfo.getPkPsnMsg());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			sb.append(" ORDER BY T.WBBEGINDATE DESC ");
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffWork.class);
		}
		return null;
	}

	@Override
	public PageInfo queryPageInfoByUecOutStaffInfo(
			UecOutStaffInfo uecOutStaffInfo, int current, int pageSize) {
		if (uecOutStaffInfo != null) {
			StringBuilder sb = new StringBuilder(
					" SELECT * FROM UEC_OUT_STAFF_INFO T  WHERE T.status_cd = ? ");
			List<Object> params = new ArrayList<Object>();
			params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPkHrorg())) {
				sb.append(" AND T.PK_HRORG = ? ");
				params.add(uecOutStaffInfo.getPkHrorg());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnCode())) {
				sb.append(" AND T.PSNCODE = ? ");
				params.add(uecOutStaffInfo.getPsnCode());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnName())) {
				sb.append(" AND T.PSNNAME LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(uecOutStaffInfo
								.getPsnName()) + "%");
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getIdNum())) {
				sb.append(" AND T.IDNUM = ? ");
				params.add(uecOutStaffInfo.getIdNum());
			}
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffInfo.class);
		}
		return null;
	}
	@Override
	public PageInfo queryPageInfoByUecOutStaffInfoVo(
			UecOutStaffInfoVo uecOutStaffInfoVo, int current, int pageSize) {
		if (null != uecOutStaffInfoVo) {
			
			String code = PlatformUtil.getCurrentUser().getScreenName().toUpperCase();
			String orgName ="";
			//String[] orgnamelist= {"呼伦","兴安","通辽","赤峰","锡林","乌兰","乌海"};
			String[] orgnamelist=null;
			String orgNametemp =null;
			if(!StrUtil.isNullOrEmpty(code)) {
				orgNametemp = PermissionUtil.getManagerOrg();
				orgnamelist=orgNametemp.split(",");
				if(orgnamelist != null && orgnamelist.length==1) {
					orgName=orgnamelist[0];
				}
			}
				//String orgName = PermissionUtil.getManagerOrg();
					StringBuilder sb = new StringBuilder(
							" SELECT * FROM VIE_UEC_OUT_STAFF_INFO T WHERE 1 = 1 ");
					List<Object> params = new ArrayList<Object>();
					if (!StrUtil.isNullOrEmpty(uecOutStaffInfoVo.getStaffName())) {
						sb.append(" AND T.STAFFNAME LIKE ? ");
						params.add("%"
								+ StringEscapeUtils.escapeSql(uecOutStaffInfoVo
										.getStaffName()) + "%");
					}
					if (!StrUtil.isNullOrEmpty(uecOutStaffInfoVo.getStaffCode())) {
						sb.append(" AND T.STAFFCODE = ? ");
						params.add(uecOutStaffInfoVo.getStaffCode());
					}
					if (!StrUtil.isNullOrEmpty(uecOutStaffInfoVo.getIdNum())) {
						sb.append(" AND T.IDNUM = ? ");
						params.add(uecOutStaffInfoVo.getIdNum());
					}
					if (!StrUtil.isNullOrEmpty(uecOutStaffInfoVo.getPkOrg())) {
						sb.append(" AND T.PKORG = ? ");
						params.add(uecOutStaffInfoVo.getPkOrg());
					}
					if (!StrUtil.isNullOrEmpty(uecOutStaffInfoVo.getPkDept())) {
						sb.append(" AND T.PKDEPT = ? ");
						params.add(uecOutStaffInfoVo.getPkDept());
					}
					if (!"区本部".equals(orgName) && !StrUtil.isNullOrEmpty(orgName)) {
						sb.append(" AND T.ORGNAME LIKE ? ");
						params.add("%" + StringEscapeUtils.escapeSql(orgName) + "%");
					}
						if(orgnamelist !=null && orgnamelist.length>1) {
							sb.append("AND (T.orgName like ? ");
							params.add("%"+orgnamelist[0]+"%");
							for(int i=1;i<orgnamelist.length-1;i++) {
								sb.append(" OR T.orgName like ? ");
								params.add("%"+orgnamelist[i]+"%");
							}
							sb.append(" OR T.orgName like ? )");
							params.add("%"+orgnamelist[orgnamelist.length-1]+"%");
						}
						//用于查询13个组织下的全部人员
						if(!StrUtil.isNullOrEmpty(uecOutStaffInfoVo.getOrgName())) {
						//为了查询带*号组织下的全部人员
						if(uecOutStaffInfoVo.getOrgName().endsWith("*")) {
								sb.append(" AND T.orgName like ? ");
								params.add("%"+uecOutStaffInfoVo.getOrgName().substring(0, 2)+"%");
		            	}
		            	else {
		            		sb.append(" AND T.orgName = ? ");
		            		params.add(uecOutStaffInfoVo.getOrgName());
		            	}
					}
						sb.append(" ORDER BY T.orgName");
						return this.jdbcFindPageInfo(sb.toString(), params, current,
								pageSize, UecOutStaffInfoVo.class);			
		}
		return null;
	}

	@Override
	public PageInfo queryPageInfoByUecOutStaffCostB(
			UecOutStaffWork uecOutStaffWork, int current, int pageSize) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_COST_B T  WHERE T.status_cd = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffWork != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffWork.getPkPsnWork())) {
				sb.append(" AND T.PK_PSN_WORK = ? ");
				params.add(uecOutStaffWork.getPkPsnWork());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffWork.getId())) {
				sb.append(" AND T.OUT_STAFF_WORK_ID = ? ");
				params.add(uecOutStaffWork.getId());
			}
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffCostB.class);
		}
		return null;
	}

	@Override
	public UecOutStaffWork findUecOutStaffWork(UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT T.*,ja3.UNITNAME unitname FROM UEC_OUT_STAFF_WORK T "
						+ " left join UEC_PSN_CORP ja3 on (T.PK_CORP = ja3.PK_CORP)"
						+ " WHERE T.status_cd = ? and T.PSN_STATUS = 0");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPkPsnMsg())) {
				sb.append(" AND T.PK_PSN_MSG = ? ");
				params.add(uecOutStaffInfo.getPkPsnMsg());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			return this.jdbcFindObject(sb.toString(), params,
					UecOutStaffWork.class);
		}
		return null;
	}

	@Override
	public List<UecOutStaffWork> queryUecOutStaffWork(
			UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_WORK T  WHERE T.status_cd = ? and T.PSN_STATUS = 1000");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);

		return this.jdbcFindList(sb.toString(), params, UecOutStaffWork.class);
	}

	@Override
	public PageInfo queryPageInfoByUecOutStaffVocqua(
			UecOutStaffInfo uecOutStaffInfo, int current, int pageSize) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_VOCQUA T  WHERE T.status_cd = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPkPsnMsg())) {
				sb.append(" AND T.PK_PSN_MSG = ? ");
				params.add(uecOutStaffInfo.getPkPsnMsg());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffVocqua.class);
		}
		return null;
	}

	@Override
	public PageInfo queryPageInfoByUecOutStaffTramsg(
			UecOutStaffInfo uecOutStaffInfo, int current, int pageSize) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_TRAMSG T  WHERE T.status_cd = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPkPsnMsg())) {
				sb.append(" AND T.PK_PSN_MSG = ? ");
				params.add(uecOutStaffInfo.getPkPsnMsg());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffTramsg.class);
		}
		return null;
	}

	@Override
	public PageInfo queryPageInfoByUecOutStaffCostH(
			UecOutStaffCostB uecOutStaffCostB, int current, int pageSize) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_COST_H T  WHERE T.status_cd = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffCostB != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffCostB.getPkPsnCostH())) {
				sb.append(" AND T.PK_PSN_COST_H = ? ");
				params.add(uecOutStaffCostB.getPkPsnCostH());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffCostB
					.getPkOutStaffCostHId())) {
				sb.append(" AND T.PK_OUT_STAFF_COST_H_ID = ? ");
				params.add(uecOutStaffCostB.getPkOutStaffCostHId());
			}
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffCostH.class);
		}
		return null;
	}

	@Override
	public PageInfo queryPageInfoByUecOutStaffManage(
			UecOutStaffCostH uecOutStaffCostH, int current, int pageSize) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_MANAGE T  WHERE T.status_cd = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffCostH != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffCostH.getPkCtManage())) {
				sb.append(" AND T.PK_CT_MANAGE = ? ");
				params.add(uecOutStaffCostH.getPkCtManage());
			} else if (!StrUtil.isNullOrEmpty(uecOutStaffCostH
					.getPkOutStaffManage())) {
				sb.append(" AND T.OUT_STAFF_MANAGE_ID = ? ");
				params.add(uecOutStaffCostH.getPkOutStaffManage());
			}
			return this.jdbcFindPageInfo(sb.toString(), params, current,
					pageSize, UecOutStaffManage.class);
		}
		return null;
	}

	@Override
	public UecOutStaffInfo findUecOutStaffInfoById(
			UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_INFO T  WHERE T.STATUS_CD = ?  ");
		List<Object> params = new ArrayList<Object>();
		params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			List<UecOutStaffInfo> list = super.jdbcFindList(sb.toString(),
					params, UecOutStaffInfo.class);
			if (list.size() > 0 && list != null) {
				return list.get(0);
			}
		}
		return null;

	}

	@Override
	public UecOutStaffInfo findUecOutStaffInfoByPsncode(String psncode) {
		if (psncode != null) {
			String sqlStr = "SELECT * FROM UEC_OUT_STAFF_INFO t WHERE t.STATUS_CD = ? AND t.PSNCODE = ? ";
			List<Object> params = new ArrayList<Object>();
			params.add(BaseUnitConstants.ENTT_STATE_ACTIVE);
			params.add(psncode);

			List<UecOutStaffInfo> list = this.jdbcFindList(sqlStr, params,
					UecOutStaffInfo.class);

			if (list != null && list.size() > 0) {
				return list.get(0);
			}
		}

		return null;
	}

	@Override
	public UecOutStaffInfo findUecOutStaffInfoOnApproval(
			UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT *  FROM UEC_OUT_STAFF_INFO T  WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		sb.append(" AND T.STATUS_CD = ? ");
		params.add(BaseUnitConstants.ENTT_STATE_PROCESS);
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnCode())) {
				sb.append(" AND T.PSNCODE = ? ");
				params.add(uecOutStaffInfo.getPsnCode());
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getPsnName())) {
				sb.append(" AND T.PSNNAME = ? ");
				params.add(StringEscapeUtils.escapeSql(uecOutStaffInfo
						.getPsnName()));
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getIdNum())) {
				sb.append(" AND T.IDNUM = ? ");
				params.add(StringEscapeUtils.escapeSql(uecOutStaffInfo
						.getIdNum()));
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getMobtelePhone())) {
				sb.append(" AND T.MOBTELEPHONE LIKE ? ");
				params.add("%"
						+ StringEscapeUtils.escapeSql(uecOutStaffInfo
								.getMobtelePhone()) + "%");
			}
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			// sb.append(" order by T2.ENDDATE desc");
			List<UecOutStaffInfo> list = super.jdbcFindList(sb.toString(),
					params, UecOutStaffInfo.class);
			if (list.size() > 0 && list != null) {
				return list.get(0);
			}
		}
		return null;

	}

	@Override
	public UecOutStaffInfo findUecOutStaffInfoByOutStaffId(
			UecOutStaffInfo uecOutStaffInfo) {
		StringBuilder sb = new StringBuilder(
				" SELECT * FROM UEC_OUT_STAFF_INFO T  WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (uecOutStaffInfo != null) {
			if (!StrUtil.isNullOrEmpty(uecOutStaffInfo.getId())) {
				sb.append(" AND T.OUT_STAFF_ID = ? ");
				params.add(uecOutStaffInfo.getId());
			}
			List<UecOutStaffInfo> list = super.jdbcFindList(sb.toString(),
					params, UecOutStaffInfo.class);
			if (list.size() > 0 && list != null) {
				return list.get(0);
			}
		}
		return null;

	}

    @Override
    public PictureTable isExitPictureTable(PictureTable pictureTable) {
        if(null != pictureTable) {
            List<Object> params = new ArrayList<Object>();
            StringBuilder sb = new StringBuilder(
                    " SELECT * FROM Picture_table T where T.staff_code = ? and T.pic_type = ?   ");
            params.add(pictureTable.getStaffCode());
            params.add(pictureTable.getPicType());
            List<PictureTable> list = super.jdbcFindList(sb.toString(),
                    params, PictureTable.class);
            if (list.size() > 0 && list != null) {
                return list.get(0);
            }
        }
        return null;
    }

    @Override
    public PictureTable getPicture(String StaffCode, String picType) {
        List<Object> params = new ArrayList<Object>();
        StringBuilder sb = new StringBuilder(
                " SELECT * FROM Picture_table T where T.staff_code = ? and T.pic_type = ?   ");
        params.add(StaffCode);
        params.add(picType);
        List<PictureTable> list = super.jdbcFindList(sb.toString(),
                params, PictureTable.class);
        if (list.size() > 0 && list != null) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public byte[] getPictureInputStream(String StaffCode, String picType) {
        List<Object> params = new ArrayList<Object>();
        StringBuilder sb = new StringBuilder(
                " SELECT * FROM Picture_table T where T.staff_code = ? and T.pic_type = ?   ");
        params.add(StaffCode);
        params.add(picType);
        List<PictureTable> list = super.jdbcFindList(sb.toString(),
                params, PictureTable.class);
        if (list.size() > 0 && list != null) {
            PictureTable pictureTable = list.get(0);
            InputStream is;
            try {
                Blob blob = pictureTable.getPicture();
                is = blob.getBinaryStream();
                ByteArrayOutputStream bytestream = new ByteArrayOutputStream(); 
                int ch; 
                try {
                    while ((ch = is.read()) != -1) { 
                        bytestream.write(ch); 
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                } 
                byte imgdata[] = bytestream.toByteArray(); 
                return imgdata;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

}
